﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MixScript.BLL;
using MixScript.BLL.Accounting;
using System.Globalization;

namespace MixScript.BLL.FI
{
    public class GenJournal : cBaseBLL
    {
        private static string msg = "";
        public GenJournal()
        { }
        /// <summary>
        /// cAccHeader
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool ListJournal(out DataTable dt, int year, int month, int type)
        {

            string Sql;
            Sql = @"SELECT h.slipid,h.slipdate as slipdate,h.slipcode,h.slipnum,h.slipcurr,h.slipdesc,ISNULL(d.Debet,0) Debet,ISNULL(d.Kredit,0) Kredit,h.posting 
                    FROM accsliphdr h LEFT JOIN  (
                    SELECT header_id,
                    sum(case dbcd when 'D' tHEN ISNULL(valconvert,0) end) as Debet,
                    sum(case dbcd when 'C' tHEN ISNULL(valconvert,0) end) as Kredit FROM accslipdet group by header_id
                    ) d ON h.slipid=d.header_id 
                    WHERE h.sliptype ='{2}' AND year(h.slipdate) = '{0}' AND month(h.slipdate) = '{1}' ";
            try
            {
                dt = Dbserver.ExecuteQuery(string.Format(Sql, year, month, type), "Journal");
                return true;
            }
            catch (Exception ex)
            { throw ex; }
        }

        /// <summary>
        /// cAccHeader
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public string getMaxJournalNo(string no, int year)
        {
            string sql;
            sql = @"select CASE LEN(ISNULL(Max(slipnum),0))
                    WHEN 6 THEN '0'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 5 THEN '00'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 4 THEN '000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 3 THEN '0000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 2 THEN '00000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    WHEN 1 THEN '000000'+ CAST(ISNULL(Max(slipnum),0) + 1 AS NVARCHAR(7))
                    ELSE ISNULL(Max(slipnum),0)
                    END from accsliphdr 
                    where slipcode Like '{0}%' and YEAR(slipdate) = {1}";

            try
            {
                return (string)Dbserver.ExecuteScalar(string.Format(sql, no, year));
            }
            catch (Exception ex)
            { throw ex; }
        }

        /// <summary>
        /// cSubsidiary
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool getSubsidiary(ref DataTable dt, string accnum)
        {
            string Sql;
            Sql = @"getSubsidiary '{0}' ";
            try
            {
                dt = Dbserver.ExecuteQuery(String.Format(Sql, accnum), "Subsidiary");
                return true;
            }
            catch (Exception ex)
            { throw ex; }

        }

        /// <summary>
        /// cAccHeader
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool saveJournal(String slipcode, string slipnum, string slipcurr, string slipdesc, DateTime slipdate, ListGeneralJournal journals)
        {

            string sql;
            int id;
            sql = @"InsertJurnalHeader '{0}','{1}','{2}','{3}','{4}'";
            id = Convert.ToInt32(Dbserver.ExecuteScalar(string.Format(sql, slipcode, slipnum, slipcurr, slipdesc, String.Format("{0:yyyy-MM-dd}", slipdate))));
            for (int i = 0; i < journals.ListOfJournal.Count; i++)
            {

                string subsql;
                subsql = @"INSERT INTO accslipdet (header_id,acctnum,
                    [desc],dbcd,currency,kurs,rate,amount,valconvert,invoice,process,subsidiary,sub_date,sub_terms,so_id)
                    VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',0)";
                string dbcd;
                string val;
                if (journals.ListOfJournal[i].debit != 0)
                {
                    dbcd = "D";
                    val = journals.ListOfJournal[i].debit.ToString();
                }
                else
                {
                    dbcd = "C";
                    val = journals.ListOfJournal[i].credit.ToString();
                }
                Dbserver.openConn();
                Dbserver.ExecuteNonQuery(String.Format(subsql,
                    id,
                    journals.ListOfJournal[i].acct,
                    journals.ListOfJournal[i].description,
                    dbcd,
                    journals.ListOfJournal[i].currency,
                    journals.ListOfJournal[i].kurs,
                    journals.ListOfJournal[i].rate,
                    journals.ListOfJournal[i].ammount,
                    val,
                    journals.ListOfJournal[i].invoice,
                    journals.ListOfJournal[i].process,
                    journals.ListOfJournal[i].subsidiary,
                    String.Format("{0:yyyy-MM-dd}", journals.ListOfJournal[i].invoiceDate),
                    journals.ListOfJournal[i].terms));
                Dbserver.CommitAndCloseConn();

            }



            return true;
        }

        /// <summary>
        /// cAccHeader
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool loadJournalhdr(string id, out DataTable dt)
        {
            try
            {
                dt = Dbserver.ExecuteQuery(string.Format("select * from accsliphdr where slipid ={0}", id), "accheader");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// cAccDetail
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public ListGeneralJournal loadJournalsub(string id)
        {
            DataTable dt = new DataTable();
            GeneralJournal gj;
            ListGeneralJournal gjss = new ListGeneralJournal();
            try
            {

                dt = Dbserver.ExecuteQuery(string.Format("select * from accslipdet where header_id ={0}", id), "accheader");
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    gj = new GeneralJournal();
                    gj.no = i + 1;
                    gj.id = i + 1;
                    int acct;
                    int.TryParse(dt.Rows[i].ItemArray[2].ToString(), out  acct);
                    gj.acct = acct;
                    gj.description = dt.Rows[i].ItemArray[3].ToString();
                    if (dt.Rows[i].ItemArray[4].ToString() == "C")
                    {
                        int val;
                        int.TryParse(dt.Rows[i].ItemArray[9].ToString(), out val);
                        gj.debit = val;
                        gj.credit = 0;
                    }
                    else
                    {
                        int val;
                        int.TryParse(dt.Rows[i].ItemArray[9].ToString(), out val);
                        gj.credit = val;
                        gj.debit = 0;
                    }
                    gj.currency = dt.Rows[i].ItemArray[5].ToString();
                    gj.kurs = dt.Rows[i].ItemArray[6].ToString();
                    float rate;
                    float.TryParse(dt.Rows[i].ItemArray[7].ToString(), out rate);
                    gj.rate = (int)rate;
                    float ammount;
                    float.TryParse(dt.Rows[i].ItemArray[8].ToString(), out ammount);
                    gj.ammount = (int)ammount;
                    float invoice;
                    float.TryParse(dt.Rows[i].ItemArray[12].ToString(), out invoice);
                    gj.invoice = (int)invoice;
                    float process;
                    float.TryParse(dt.Rows[i].ItemArray[13].ToString(), out process);
                    gj.process = (int)process;
                    gj.subsidiary = dt.Rows[i].ItemArray[14].ToString();
                    gj.invoiceDate = DateTime.Parse(dt.Rows[i].ItemArray[15].ToString());
                    gj.terms = dt.Rows[i].ItemArray[16].ToString();
                    if (gjss.ListOfJournal == null)
                        gjss.ListOfJournal = new List<GeneralJournal>();
                    gjss.ListOfJournal.Add(gj);
                    
                }

                return gjss;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

    }
}
